Attributes of Relationship Types

Attributes of relationship types#

Relationship types can also have attributes, similar to those of entity types. Hence the relationship WORKS_ON between EMPLOYEE and PROJECT has an attribute Start_Date. This is illustrated in the diagram below:

Generally, it is not recommended to give attributes to relationships if it’s not required because when we represent this ER diagram in the database, we don’t want to create a separate table for each relationship with attributes as this will create complexity.

So we want to move the attributes from the relationship to either of the two entities joined by the relationship. Let’s tackle this problem case by case with the help of examples:

The one to one relationship#

In the COMPANY database, an employee manages a department and each department is managed by an employee. Now, if we want to store the Start_Date from which the employee started managing the department then we may think that we can give the Start_Date attribute to the relationship MANAGES. But, in this case, we may avoid it by associating the Start_Date attribute to either the EMPLOYEE or DEPARTMENT entity.

The one to many relationship#

In our Company database, many employees can work for a department but each employee can work only for a single department. So, there is a one to many relationship between these entities.

Now if we want to store the Start_Date when the employee started working for the department, then instead of assigning it to the relationship we should assign it to the EMPLOYEE entity. Assigning it to the EMPLOYEE entity makes sense as each employee can work for a single department only, but on the other hand, one department can have many employees. Hence, it wouldn’t make sense if we assign the Start_Date attribute to the DEPARTMENT entity.

The many to many relationship#

In our Company database, an employee can work on many projects simultaneously and each project can have many employees working on it. Hence, it’s a many to many relationship. So here assigning the Start_Date to the employee will not work as we will not know when an employee starts working on a specific project because a single employee can work on multiple projects each with its own starting date. It is the same case with the PROJECT entity. Hence, we are forced to assign the Start_Date attribute to the relationship WORKS_ON.

In the next lesson, we will learn about the last component in an ER model.